package com.googlecode.allinclusive.tools.srcgen.database;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.sql.DataSource;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.googlecode.allinclusive.tools.srcgen.converter.NameConverter;

public class DatabaseIntrospector {

	private static final Log log = LogFactory.getLog(DatabaseIntrospector.class);  
	
	private boolean forceBigDecimals = false;
	
    private DatabaseMetaData databaseMetaData;
    
    private NameConverter nameConverter;
    
    public DatabaseIntrospector() {}
	
    public List<TableDefinition> introspectTables(String catalog, String schema) {
    	
    	log.debug("introspectTables.");
    	
    	// 表定义对象
		List<TableDefinition> tableDefinitions = new ArrayList<TableDefinition>();
		
        ResultSet rs = null;
    	try {
			rs = databaseMetaData.getTables(catalog, schema, null, new String[]{"VIEW", "TABLE"});
			while(rs.next()) {
				TableDefinition td = new TableDefinition(catalog, schema, rs.getString("TABLE_NAME"));
				if (td.getTableName() == null || "".equals(td.getTableName().trim())) {
					continue;
				}
				
		    	// Description
				td.setDescription(rs.getString("REMARKS"));
		    	// JavaClass
		    	td.setJavaClass(nameConverter.generateJavaClass(td.getTableName()));

		    	tableDefinitions.add(td);
			}
			
		} catch (SQLException e) {
			log.error(e.getMessage());
		} finally {
			closeResultSet(rs);
		}
    	
		introspectTables(tableDefinitions);
		
    	return tableDefinitions;
    }
	
    private void introspectTables(List<TableDefinition> tableDefinitions) {
    	for (TableDefinition tableDefinition : tableDefinitions) {
    		getColumns(tableDefinition);
    		calculatePrimaryKey(tableDefinition);
    	}
    }
	
    private void getColumns(TableDefinition tableDefinition) {
        ResultSet rs = null;

        try {
            rs = databaseMetaData.getColumns(tableDefinition.getCatalog(),
            		tableDefinition.getSchema(),
            		tableDefinition.getTableName(),
            		null);
        } catch (SQLException e) {
        	e.printStackTrace();
            closeResultSet(rs);
            return;
        }

        try {
            while (rs.next()) {
            	
                ColumnDefinition cd = new ColumnDefinition();

                cd.setJdbcType(rs.getInt("DATA_TYPE")); //$NON-NLS-1$
                cd.setLength(rs.getInt("COLUMN_SIZE")); //$NON-NLS-1$
                cd.setActualColumnName(rs.getString("COLUMN_NAME")); //$NON-NLS-1$
                cd.setNullable(rs.getInt("NULLABLE") == DatabaseMetaData.columnNullable); //$NON-NLS-1$
                cd.setScale(rs.getInt("DECIMAL_DIGITS")); //$NON-NLS-1$
                cd.setTypeName(rs.getString("TYPE_NAME")); //$NON-NLS-1$
                cd.setDescription(rs.getString("REMARKS"));

                // JavaProperty
                cd.setJavaProperty(nameConverter.generateJavaProperty(cd.getActualColumnName()));
                // JavaType
                initializeResolvedJavaType(cd);
                
                tableDefinition.getColumnDefinitions().addColumn(cd);
            }
        } catch (SQLException e) {
        	log.error(e.getMessage());
        } finally {
            closeResultSet(rs);
        }
    }
    
    private void calculatePrimaryKey(TableDefinition tableDefinition) {
        ResultSet rs = null;

        try {
            rs = databaseMetaData.getPrimaryKeys(tableDefinition.getCatalog(),
            		tableDefinition.getSchema(),
            		tableDefinition.getTableName());
        } catch (SQLException e) {
            closeResultSet(rs);
            return;
        }

        try {
            while (rs.next()) {
                String columnName = rs.getString("COLUMN_NAME");
                
                tableDefinition.getColumnDefinitions().addPrimaryKeyColumn(columnName);
            }
        } catch (SQLException e) {
        	log.error(e.getMessage());
        } finally {
            closeResultSet(rs);
        }
    }
    
    private void initializeResolvedJavaType(ColumnDefinition cd) {

		JavaTypeResolver type = null;
		switch (cd.getJdbcType()) {
		case Types.ARRAY:
			type = new JavaTypeResolver(Object.class.getName());
			type.setJdbcTypeName("ARRAY"); //$NON-NLS-1$
			break;
		case Types.BIGINT:
			type = new JavaTypeResolver(Long.class.getName());
			type.setJdbcTypeName("BIGINT"); //$NON-NLS-1$
			break;
		case Types.BINARY:
			type = new JavaTypeResolver("byte[]");			
			type.setJdbcTypeName("BINARY"); //$NON-NLS-1$
			break;	
		case Types.BIT:
			type = new JavaTypeResolver(Boolean.class.getName());
			type.setJdbcTypeName("BIT"); //$NON-NLS-1$
			break;
		case Types.BLOB:
			type = new JavaTypeResolver("byte[]");
			type.setJdbcTypeName("BLOB"); //$NON-NLS-1$
			break;
		case Types.BOOLEAN:
			type = new JavaTypeResolver(Boolean.class.getName());
			type.setJdbcTypeName("BOOLEAN"); //$NON-NLS-1$
			break;
		case Types.CHAR:
			type = new JavaTypeResolver(String.class.getName());
			type.setJdbcTypeName("CHAR"); //$NON-NLS-1$
			break;
		case Types.CLOB:
			type = new JavaTypeResolver(String.class.getName());
			type.setJdbcTypeName("CLOB"); //$NON-NLS-1$
			break;
		case Types.DATALINK:
			type = new JavaTypeResolver(Object.class.getName());
			type.setJdbcTypeName("DATALINK"); //$NON-NLS-1$
			break;		
		case Types.DATE:
			type = new JavaTypeResolver(Date.class.getName());
			type.setJdbcTypeName("DATE"); //$NON-NLS-1$
			break;
		case Types.DECIMAL:
			if (cd.getScale() > 0 || cd.getLength() > 18 || forceBigDecimals) {
				type = new JavaTypeResolver(BigDecimal.class.getName());
			} else if (cd.getLength() > 9) {
				type = new JavaTypeResolver(Long.class.getName());
			} else if (cd.getLength() > 4) {
				type = new JavaTypeResolver(Integer.class.getName());
			} else {
				type = new JavaTypeResolver(Short.class.getName());
			}
			type.setJdbcTypeName("DECIMAL"); //$NON-NLS-1$
			break;
		case Types.DISTINCT:
			type = new JavaTypeResolver(Object.class.getName());
			type.setJdbcTypeName("DISTINCT"); //$NON-NLS-1$
			break;
		case Types.DOUBLE:
			type = new JavaTypeResolver(Double.class.getName());
			type.setJdbcTypeName("DOUBLE"); //$NON-NLS-1$
			break;
		case Types.FLOAT:
			type = new JavaTypeResolver(Double.class.getName());
			type.setJdbcTypeName("FLOAT"); //$NON-NLS-1$
			break;
		case Types.INTEGER:
			type = new JavaTypeResolver(Integer.class.getName());
			type.setJdbcTypeName("INTEGER"); //$NON-NLS-1$
			break;
		case Types.JAVA_OBJECT:
			type = new JavaTypeResolver(Object.class.getName());
			type.setJdbcTypeName("JAVA_OBJECT"); //$NON-NLS-1$
			break;
		case Types.LONGVARBINARY:
			type = new JavaTypeResolver("byte[]");
			type.setJdbcTypeName("LONGVARBINARY"); //$NON-NLS-1$
			break;
		case Types.LONGVARCHAR:
			type = new JavaTypeResolver(String.class.getName());
			type.setJdbcTypeName("LONGVARCHAR"); //$NON-NLS-1$
			break;
		case Types.NULL:
			type = new JavaTypeResolver(Object.class.getName());
			type.setJdbcTypeName("NULL"); //$NON-NLS-1$
			break;
		case Types.NUMERIC:
			if (cd.getScale() > 0 || cd.getLength() > 18 || forceBigDecimals) {
				type = new JavaTypeResolver(BigDecimal.class.getName());
			} else if (cd.getLength() > 9) {
				type = new JavaTypeResolver(Long.class.getName());
			} else if (cd.getLength() > 4) {
				type = new JavaTypeResolver(Integer.class.getName());
			} else {
				type = new JavaTypeResolver(Short.class.getName());
			}
			type.setJdbcTypeName("NUMERIC"); //$NON-NLS-1$
			break;
		case Types.OTHER:
			type = new JavaTypeResolver(Object.class.getName());
			type.setJdbcTypeName("OTHER"); //$NON-NLS-1$
			break;
		case Types.REAL:
			type = new JavaTypeResolver(Float.class.getName());
			type.setJdbcTypeName("REAL"); //$NON-NLS-1$
			break;
		case Types.REF:
			type = new JavaTypeResolver(Object.class.getName());
			type.setJdbcTypeName("REF"); //$NON-NLS-1$
			break;
		case Types.SMALLINT:
			type = new JavaTypeResolver(Short.class.getName());
			type.setJdbcTypeName("SMALLINT"); //$NON-NLS-1$
			break;
		case Types.STRUCT:
			type = new JavaTypeResolver(Object.class.getName());
			type.setJdbcTypeName("STRUCT"); //$NON-NLS-1$
			break;
		case Types.TIME:
			type = new JavaTypeResolver(Date.class.getName());
			type.setJdbcTypeName("TIME"); //$NON-NLS-1$
			break;
		case Types.TIMESTAMP:
			type = new JavaTypeResolver(Date.class.getName());
			type.setJdbcTypeName("TIMESTAMP"); //$NON-NLS-1$
			break;
		case Types.TINYINT:
			type = new JavaTypeResolver(Byte.class.getName());
			type.setJdbcTypeName("TINYINT"); //$NON-NLS-1$
			break;
		case Types.VARBINARY:
			type = new JavaTypeResolver("byte[]");
			type.setJdbcTypeName("VARBINARY"); //$NON-NLS-1$
			break;
		case Types.VARCHAR:
			type = new JavaTypeResolver(String.class.getName());
			type.setJdbcTypeName("VARCHAR"); //$NON-NLS-1$
			break;
		default:
			type = new JavaTypeResolver(String.class.getName());
			type.setJdbcTypeName("VARCHAR"); //$NON-NLS-1$
			break;
		}

		cd.setJavaTypeResolver(type);
	}
	
    private void closeResultSet(ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                // ignore
                ;
            }
        }
    }

    // --- getter, setter ---
	public void setForceBigDecimals(boolean forceBigDecimals) {
		this.forceBigDecimals = forceBigDecimals;
	}
	public void setDataSource(DataSource ds) throws SQLException {
		Connection connection = ds.getConnection();
		databaseMetaData = connection.getMetaData();
	}
	public void setNameConverter(NameConverter nameConverter) {
		this.nameConverter = nameConverter;
	}    
}
